package hms.inventory;

import hms.database.DAO;
import hms.database.DAOnoGUI;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;

import javax.swing.table.AbstractTableModel;

/**
 * <h2>Class Overview</h2> Inventory is a collection of inventoryEntries owned
 * by a by a hotel. The inventoryEntries are added and edited but not deleted
 * for auditing purposes. An audit trail is a record of all completed inventory
 * operations. An Inventory object contains a list of InventoryEntry entries.
 * 
 * An InventoryEntry is an object used and owned by a hotel. InventoryEntry is
 * composed of the following: name, category, vendor, size, quantity, and price.
 * 
 * @author Akilah McIntyre
 */
public class Inventory extends AbstractTableModel {

	private static final long serialVersionUID = -4370299731880557646L;

	private ArrayList<InventoryEntry> inventoryEntries;

	public Inventory() {
		super();
		inventoryEntries = new ArrayList<InventoryEntry>();
	}

	// Names of the columns
	static public String[] m_colNames = { "ENTRY_ID", "ENTRY_NAME","ITEM", 
			"CATEGORY", "COLOR", "VENDOR", "SIZE", "PRICE", "QUANTITY" };
	/*
	 * { "INV_ENTRY_ID", "INV_ENTRY_NAME",
			"CATALOG_ITEM_ID", "CATALOG_NAME", "ITEM_CATEGORY_ID",
			"ITEM_CATEGORY_NAME", "COLOR_ID", "COLOR_NAME", "VENDOR_ID",
			"VENDOR_NAME", "SIZE_ID", "SIZE_NAME", "PRICE", "QUANTITY" };
	 */

	// Types of the columns.
	static public Class[] m_colTypes = { Integer.class, String.class,String.class,
		String.class,String.class,String.class,String.class, Double.class, Integer.class };

	/**
	 * Initialize inventory entries from database
	 */
	/*
	 * SELECT INV_ENTRY_ID, INV_ENTRY_NAME, PRICE, QUANTITY, ITEM_NAME_ID,
	 * ITEM_NAME, VENDOR_ID, VENDOR_NAME, SIZE_ID, SIZE_NAME, COLOR_ID,
	 * COLOR_NAME, ITEM_CATEGORY_ID, ITEM_CATEGORY_NAME FROM VW_INVENTORY_ITEMS
	 */
	@SuppressWarnings("unused")
	public void initEntries() {
		DAO localDAO = DAOnoGUI.getDAO();
		// get data from database associated with dao
		ResultSet rsEntries = localDAO
				.getResult("SELECT INV_ENTRY_ID, INV_ENTRY_NAME, PRICE, QUANTITY, ITEM_NAME_ID, ITEM_NAME, VENDOR_ID, VENDOR_NAME, SIZE_ID, SIZE_NAME, COLOR_ID, COLOR_NAME, ITEM_CATEGORY_ID, ITEM_CATEGORY_NAME FROM VW_INVENTORY_ITEMS");
		try {
			// Create InvColor instance from database
			while (rsEntries.next()) {
				String inv_entry_name = null;
				int inv_entry_id = -1;
				String color_name = null;
				int color_id = -1;
				String vendor_name = null;
				int vendor_id = -1;
				String size_name = null;
				int size_id = -1;
				String catalog_item_name = null;
				int catalog_item_id = -1;
				String item_category_name = null;
				int item_category_id = -1;

				Vendor vendor = null;
				CatalogItem catItem = null;
				ItemCategory itemCategory = null;
				InvColor color = null;
				Size size = null;

				inv_entry_name = rsEntries.getString("INV_ENTRY_NAME");

				if (inv_entry_name != null) {
					InventoryEntry entry = new InventoryEntry();
					inv_entry_id = rsEntries.getInt("INV_ENTRY_ID");
					entry.setInvEntryId(inv_entry_id);
					entry.setInvEntryName(inv_entry_name);

					vendor_name = rsEntries.getString("vendor_NAME");
					if (vendor_name != null) {
						vendor_id = rsEntries.getInt("vendor_ID");
						vendor = new Vendor(vendor_id, vendor_name);
						entry.setVendor(vendor);
					}

					color_name = rsEntries.getString("color_NAME");
					if (color_name != null) {
						color_id = rsEntries.getInt("color_ID");
						color = new InvColor(color_id, color_name);
						entry.setColor(color);
					}

					size_name = rsEntries.getString("size_NAME");
					if (size_name != null) {
						size_id = rsEntries.getInt("size_ID");
						size = new Size(size_id, size_name);
						entry.setSize(size);
					}

					item_category_name = rsEntries
							.getString("item_category_NAME");
					if (item_category_name != null) {
						item_category_id = rsEntries.getInt("item_category_ID");
						itemCategory = new ItemCategory(item_category_id,
								item_category_name);
						entry.setItemCategory(itemCategory);
					}

					catalog_item_name = rsEntries
							.getString("item_NAME");
					if (catalog_item_name != null) {
						catalog_item_id = rsEntries.getInt("item_NAME_ID");
						catItem = new CatalogItem(catalog_item_id,
								catalog_item_name);
						entry.setCatalogItem(catItem);
					}

					double price = rsEntries.getDouble("PRICE");
					entry.setPrice(price);

					int quantity = rsEntries.getInt("QUANTITY");
					entry.setQuantity(quantity);

					addEntry(entry);
				}
			}
		} catch (SQLException e) {
			// TODO [Akilah] Write to view or popup dialog
			e.printStackTrace();
		}
		DAOnoGUI.closeDAO();
	}

	/**
	 * 
	 */
	@Override
	public int getRowCount() {
		return this.inventoryEntries.size();
	}

	@Override
	public int getColumnCount() {
		return m_colNames.length;
	}

	public String getColumnName(int col) {
		return m_colNames[col];
	}

	@SuppressWarnings("unchecked")
	public Class getColumnClass(int col) {
		return m_colTypes[col];
	}
			
			/*
	 * "INV_ENTRY_ID", "INV_ENTRY_NAME", "CATALOG_ITEM_ID", "CATALOG_NAME",
	 * "ITEM_CATEGORY_ID", "ITEM_CATEGORY_NAME", "COLOR_ID", "COLOR_NAME",
	 * "VENDOR_ID", "VENDOR_NAME", "SIZE_ID", "SIZE_NAME" "PRICE", "QUANTITY"
	 */
	@Override
	public Object getValueAt(int row, int column) {
		InventoryEntry entry = this.inventoryEntries.get(row);
		String name = (entry.getInvEntryName() != null) ? entry.getInvEntryName(): "";
		String catalog = (entry.getCatalogItem() != null) ? entry.getCatalogItem().catlg_item_name:"";
		String category = (entry.getItemCategory() != null) ? entry.getItemCategory().itemCategory_name:"";
		String color = (entry.getColor() != null) ?  entry.getColor().color_name:"";
		String size = (entry.getSize() != null) ?  entry.getSize().size_name:"";		
		String vendor = (entry.getVendor() != null) ?  entry.getVendor().vendor_name:"";	
		
		switch (column) {
		case 0: // INV_ENTRY_ID
			return entry.getInvEntryId();
		case 1: // INV_ENTRY_NAME
			return name;
		case 2: // CATALOG_ITEM_NAME
			return catalog;
		case 3: // CATALOG_ITEM_NAME
			return category;
		case 4: // COLOR_NAME
			return color;
		case 5: // VENDOR_NAME
			return vendor;
		case 6: // SIZE_NAME
			return size;
		case 7: // PRICE
			return entry.getPrice();
		case 8: // QUANTITY
			return entry.getQuantity();
		}

		return new String();
	}
/*
	public void setValueAt(Object value, int row, int col) {
		InventoryEntry entry = this.inventoryEntries.get(row);
		switch (col) {
		case 0: // INV_ENTRY_ID
			entry.setInvEntryId((Integer) value);
			break;
		case 1: // INV_ENTRY_NAME
			entry.setInvEntryName((String) value);
			break;
		case 2: // CATALOG_ITEM_ID
			int catalog_item_id = (Integer) value;
			String catalog_item_name = entry.getCatalogItem().catlg_item_name;
			CatalogItem catItem = new CatalogItem(catalog_item_id,
					catalog_item_name);
			entry.setCatalogItem(catItem);
			break;
		case 3: // CATALOG_ITEM_NAME
			catalog_item_id = entry.getCatalogItem().catlg_item_id;
			catalog_item_name = (String) value;
			catItem = new CatalogItem(catalog_item_id, catalog_item_name);
			entry.setCatalogItem(catItem);
			break;
		case 4: // COLOR_ID
			int color_id = (Integer) value;
			String color_name = entry.getColor().color_name;
			InvColor color = new InvColor(color_id, color_name);
			entry.setColor(color);
			break;
		case 5: // COLOR_NAME
			color_id = entry.getColor().color_id;
			color_name = (String) value;
			color = new InvColor(color_id, color_name);
			entry.setColor(color);
			break;
		case 6: // VENDOR_ID
			int vendor_id = (Integer) value;
			String vendor_name = entry.getVendor().vendor_name;
			Vendor vendor = new Vendor(vendor_id, vendor_name);
			entry.setVendor(vendor);
			break;
		case 7: // VENDOR_NAME
			vendor_id = entry.getVendor().vendor_id;
			vendor_name = (String) value;
			vendor = new Vendor(vendor_id, vendor_name);
			entry.setVendor(vendor);
			break;
		case 8: // SIZE_ID
			int size_id = (Integer) value;
			String size_name = entry.getSize().size_name;
			Size size = new Size(size_id, size_name);
			entry.setSize(size);
			break;
		case 9: // SIZE_NAME
			size_id = entry.getSize().size_id;
			size_name = (String) value;
			size = new Size(size_id, size_name);
			entry.setSize(size);
			break;
		case 10: // PRICE
			double price = (Double) value;
			entry.setPrice(price);
			break;
		case 11: // QUANTITY
			int quantity = (Integer) value;
			entry.setQuantity(quantity);
			break;
		}

		changeEntry(entry, row);
	}
*/

	/**
	 * @return the inventoryEntries
	 */
	public ArrayList<InventoryEntry> getInventoryItems() {
		return inventoryEntries;
	}
	
	/**
	 * Sets or initialize the collection of inventory inventoryEntries
	 * 
	 * <p>
	 * The catalog inventoryEntries will be retrieved from external storage
	 * 
	 * @param inventoryEntries
	 *            the catItems to set
	 */
	public void setInventoryItems(ArrayList<InventoryEntry> inventoryEntries) {
		this.inventoryEntries = inventoryEntries;
	}

	public boolean addDbEntry(InventoryEntry entry) {
		String tblName = entry.getTableName();
		String tblIDCol = entry.getUniqueIdentifierFieldName();
		boolean isSuccess = true;
		if (entry.getInvEntryName() == null
				|| entry.getInvEntryName().equals("New Inventory Entry")) {
			entry.setInvEntryName("Entry #"
					+ new Long(Calendar.getInstance().getTimeInMillis())
							.toString());
		}
		try {
			if (entry.getCatalogItem() == null) {
				isSuccess = false;
			} else {
				String insertSQL = "INSERT INTO INVENTORY(INV_ENTRY_NAME, ITEM_NAME_ID) VALUES(?,?)";
				DAO localDAO = DAOnoGUI.getDAO();
				PreparedStatement ps = localDAO
						.generatePreparedStatement(insertSQL);
				ps.setString(1, entry.getInvEntryName());
				ps.setInt(2, entry.getCatalogItem().catlg_item_id);
				ps.execute();

				int new_id = localDAO.getDbId(tblName, "INV_ENTRY_NAME",
						tblIDCol, entry.getInvEntryName());
				if (new_id != -1) {

					isSuccess &= updateNonIdInvFields(entry, tblName, tblIDCol,
							localDAO, new_id);

					if (isSuccess) {
						entry.setInvEntryId(new_id);
						this.inventoryEntries.add(entry);
					}
				} else {
					isSuccess = false;
				}
				DAOnoGUI.closeDAO();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.fireTableDataChanged();
		return isSuccess;
	}

	private boolean updateNonIdInvFields(InventoryEntry entry, String tblName,
			String tblIDCol, DAO localDAO, int inv_entry_id) {
		boolean isSuccess = true;
		if (entry.getSize() != null) {
			isSuccess &= localDAO.updateDBTableValueParam(tblName, "SIZE_ID",
					tblIDCol, entry.getSize().size_id, inv_entry_id, "Integer");
		}
		else{
			isSuccess &= localDAO.updateCommand("UPDATE INVENTORY SET SIZE_ID=NULL WHERE "+ tblIDCol+"="+inv_entry_id);
		}
		if (entry.getColor() != null) {
			isSuccess &= localDAO.updateDBTableValueParam(tblName, "COLOR_ID",
					tblIDCol, entry.getColor().color_id, inv_entry_id,
					"Integer");
		}
		else{
			isSuccess &= localDAO.updateCommand("UPDATE INVENTORY SET COLOR_ID=NULL WHERE "+ tblIDCol+"="+inv_entry_id);
		}
		if (entry.getVendor() != null) {
			isSuccess &= localDAO.updateDBTableValueParam(tblName, "VENDOR_ID",
					tblIDCol, entry.getVendor().vendor_id, inv_entry_id,
					"Integer");
		}
		else{
			isSuccess &= localDAO.updateCommand("UPDATE INVENTORY SET VENDOR_ID=NULL WHERE "+ tblIDCol+"="+inv_entry_id);
		}
		if (entry.getItemCategory() != null) {
			isSuccess &= localDAO.updateDBTableValueParam(tblName,
					"ITEM_CATEGORY_ID", tblIDCol,
					entry.getItemCategory().itemCategory_id, inv_entry_id,
					"Integer");
		}
		else{
			isSuccess &= localDAO.updateCommand("UPDATE INVENTORY SET ITEM_CATEGORY_ID=NULL WHERE "+ tblIDCol+"="+inv_entry_id);
		}
		
		if (entry.getCatalogItem() != null) {
			localDAO.updateDBTableValueParam(tblName, "ITEM_NAME_ID", tblIDCol,
					entry.getCatalogItem().catlg_item_id, inv_entry_id,
					"Integer");
		}
		else{
			isSuccess &= localDAO.updateCommand("UPDATE INVENTORY SET ITEM_CATEGORY_ID=99 WHERE "+ tblIDCol+"="+inv_entry_id);
		}
		isSuccess &= localDAO.updateDBTableValueParam(tblName, "PRICE",
				tblIDCol, entry.getPrice(), inv_entry_id, "Double");
		isSuccess &= localDAO.updateDBTableValueParam(tblName, "QUANTITY",
				tblIDCol, entry.getQuantity(), inv_entry_id, "Integer");
		return isSuccess;
	}

	public void addEntry(InventoryEntry entry) {
		this.inventoryEntries.add(entry);
		this.fireTableDataChanged();
	}

	public boolean updateDBEntry(InventoryEntry entry, int selIndex) {
		String tblName = entry.getTableName();
		String tblIDCol = entry.getUniqueIdentifierFieldName();
		boolean isSuccess = true;
		if (entry.getCatalogItem() == null) {
			isSuccess = false;
		} else if (entry.getInvEntryName() == null) {
			isSuccess = false;
		} else {
			DAO localDAO = DAOnoGUI.getDAO();
			int inv_entry_id = this.inventoryEntries.get(selIndex).getInvEntryId();

			isSuccess &= localDAO.updateDBTableValueParam(tblName,
					"INV_ENTRY_NAME", tblIDCol, entry.getInvEntryName(),
					inv_entry_id, "String");

			isSuccess &= updateNonIdInvFields(entry, tblName, tblIDCol,
					localDAO, inv_entry_id);
			DAOnoGUI.closeDAO();
			if (isSuccess) {
				changeEntry(entry, selIndex);
			}
		}
		this.fireTableDataChanged();
		
		return isSuccess;
	}

	public void changeEntry(InventoryEntry entry, int selIndex) {
		this.inventoryEntries.set(selIndex, entry);
	}

	public boolean deleteDbEntry(InventoryEntry entry, int selIndex) {
		boolean isSuccess = true;
		int inv_entry_id = -1;
		String inv_entry_name = null;
		try {
			DAO localDAO = DAOnoGUI.getDAO();
			inv_entry_id = this.inventoryEntries.get(selIndex).getInvEntryId();
			inv_entry_name = this.inventoryEntries.get(selIndex).getInvEntryName();
			isSuccess &= localDAO.deleteDBTableValueParam("INVENTORY",
					"INV_ENTRY_NAME", "INV_ENTRY_ID", entry.getInvEntryName(),
					inv_entry_id);
			if (isSuccess) {
				deleteEntry(selIndex);
			}
			DAOnoGUI.closeDAO();
		} catch (Exception ex) {
			isSuccess = false;
		}
		this.fireTableDataChanged();
		
		return isSuccess;
	}

	/**
	 * @param selIndex
	 *            Index of target color
	 */
	public void deleteEntry(int selIndex) {
		this.inventoryEntries.remove(selIndex);
	}
}
